CREATE trigger [dbo].[projectreportsUpdateApprovedByFinance] on [dbo].[projectreports] for update as begin
--Increase change notifications to include when financial reports, IMPs and audits are approved
--A change notification should appear when: financial reports are approved; IMPS are approved; AASS are approved; and interim and financial audits are approved.
--The Financial Report # <Financial Report ID> of Project # <Project ID> was approved on <lastupdatedate>.
DECLARE @message varchar(255);
DECLARE @projectid varchar(100);
DECLARE @id int;
DECLARE @name varchar(100);

IF EXISTS (SELECT *FROM INSERTED i JOIN DELETED d ON i.id = d.id 
					WHERE i.approvedbyfinance IS NOT NULL AND i.approvedbyfinance='1' 
					AND (d.approvedbyfinance IS NULL OR i.approvedbyfinance<>d.approvedbyfinance))
	begin
		select @id=id,@projectid=projectid  
		from INSERTED;
		
		set @message='The Financial Report #'+CAST(@id as varchar(20))+' of Project #'+CAST(@projectid as varchar(100))+
		' was approved on '+ convert(varchar,GETDATE(),1);
		insert into changenotificationsmessage(message,date,url,changenotificationsid,programofficer,regionaldirector,programassistant,regionid)
		values(@message,GETDATE(),'showAddReportingrequirements.do?cipeprojectId='+cast(@projectid as varchar(20)),3,null,null,null,null);
	end

end;
GO

CREATE trigger [dbo].[impInsertIMPApproved] on [dbo].[imp] for insert as begin
--Increase change notifications to include when financial reports, IMPs and audits are approved
--A change notification should appear when: financial reports are approved; IMPS are approved; AASS are approved; and interim and financial audits are approved.
--The IMP # <IMP ID> of Project # <Project ID> was approved on <lastupdatedate>.
DECLARE @message varchar(255);
DECLARE @projectid varchar(100);
DECLARE @id int;
DECLARE @name varchar(100);

IF EXISTS (SELECT *FROM INSERTED i
					WHERE i.impperformed IS NOT NULL)
	begin
		select @id=i.id,@projectid=project.id  
		from INSERTED i
		JOIN audits ON i.auditsid = audits.id
		JOIN project ON audits.projectid = project.id;
		
		set @message='The IMP #'+CAST(@id as varchar(20))+' of Project #'+CAST(@projectid as varchar(100))+
		' was approved on '+ convert(varchar,GETDATE(),1);
		insert into changenotificationsmessage(message,date,url,changenotificationsid,programofficer,regionaldirector,programassistant,regionid)
		values(@message,GETDATE(),'auditsForm.do?cipeprojectId='+cast(@projectid as varchar(20)),3,null,null,null,null);
	end

end;
GO

CREATE trigger [dbo].[auditsUpdateIAASSApproved] on [dbo].[audits] for update as begin
--Increase change notifications to include when financial reports, IMPs and audits are approved
--A change notification should appear when: financial reports are approved; IMPS are approved; AASS are approved; and interim and financial audits are approved.
--The AASS of Interim Audit # <Interim Audit ID> of Project # <Project ID> was approved on <lastupdatedate>.
DECLARE @message varchar(255);
DECLARE @projectid varchar(100);
DECLARE @id int;
DECLARE @name varchar(100);

IF EXISTS (SELECT *FROM INSERTED i JOIN DELETED d ON i.id = d.id 
					WHERE i.iaselectionmemoapproved IS NOT NULL
					AND (d.iaselectionmemoapproved IS NULL OR i.iaselectionmemoapproved<>d.iaselectionmemoapproved))
	begin
		select @id=i.id,@projectid=project.id  
		from INSERTED i
		JOIN project ON i.projectid = project.id;
		
		set @message='The AASS of Interim Audit #'+CAST(@id as varchar(20))+' of Project #'+CAST(@projectid as varchar(100))+
		' was approved on '+ convert(varchar,GETDATE(),1);
		insert into changenotificationsmessage(message,date,url,changenotificationsid,programofficer,regionaldirector,programassistant,regionid)
		values(@message,GETDATE(),'auditsForm.do?cipeprojectId='+cast(@projectid as varchar(20)),3,null,null,null,null);
	end

end;
GO

CREATE trigger [dbo].[auditsUpdateIAApproved] on [dbo].[audits] for update as begin
--Increase change notifications to include when financial reports, IMPs and audits are approved
--A change notification should appear when: financial reports are approved; IMPS are approved; AASS are approved; and interim and financial audits are approved.
--The Interim Audit # <Interim Audit ID> of Project # <Project ID> was approved on <lastupdatedate>.
DECLARE @message varchar(255);
DECLARE @projectid varchar(100);
DECLARE @id int;
DECLARE @name varchar(100);

IF EXISTS (SELECT *FROM INSERTED i JOIN DELETED d ON i.id = d.id 
					WHERE i.iaapproved IS NOT NULL 
					AND (d.iaapproved IS NULL OR i.iaapproved<>d.iaapproved))
	begin
		select @id=i.id,@projectid=project.id  
		from INSERTED i
		JOIN project ON i.projectid = project.id;
		
		set @message='The Interim Audit #'+CAST(@id as varchar(20))+' of Project #'+CAST(@projectid as varchar(100))+
		' was approved on '+ convert(varchar,GETDATE(),1);
		insert into changenotificationsmessage(message,date,url,changenotificationsid,programofficer,regionaldirector,programassistant,regionid)
		values(@message,GETDATE(),'auditsForm.do?cipeprojectId='+cast(@projectid as varchar(20)),3,null,null,null,null);
	end

end;
GO

CREATE trigger [dbo].[auditsUpdateFAASSApproved] on [dbo].[audits] for update as begin
--Increase change notifications to include when financial reports, IMPs and audits are approved
--A change notification should appear when: financial reports are approved; IMPS are approved; AASS are approved; and interim and financial audits are approved.
--The AASS of Final Audit # <Final Audit ID> of Project # <Project ID> was approved on <lastupdatedate>.
DECLARE @message varchar(255);
DECLARE @projectid varchar(100);
DECLARE @id int;
DECLARE @name varchar(100);

IF EXISTS (SELECT *FROM INSERTED i JOIN DELETED d ON i.id = d.id 
					WHERE i.faselectionmemoapproved IS NOT NULL
					AND (d.faselectionmemoapproved IS NULL OR i.faselectionmemoapproved<>d.faselectionmemoapproved))
	begin
		select @id=i.id,@projectid=project.id  
		from INSERTED i
		JOIN project ON i.projectid = project.id;
		
		set @message='The AASS of Final Audit #'+CAST(@id as varchar(20))+' of Project #'+CAST(@projectid as varchar(100))+
		' was approved on '+ convert(varchar,GETDATE(),1);
		insert into changenotificationsmessage(message,date,url,changenotificationsid,programofficer,regionaldirector,programassistant,regionid)
		values(@message,GETDATE(),'auditsForm.do?cipeprojectId='+cast(@projectid as varchar(20)),3,null,null,null,null);
	end

end;
GO

CREATE trigger [dbo].[auditsUpdateFAApproved] on [dbo].[audits] for update as begin
--Increase change notifications to include when financial reports, IMPs and audits are approved
--A change notification should appear when: financial reports are approved; IMPS are approved; AASS are approved; and interim and financial audits are approved.
--The Final Audit # <Final Audit ID> of Project # <Project ID> was approved on <lastupdatedate>.
DECLARE @message varchar(255);
DECLARE @projectid varchar(100);
DECLARE @id int;
DECLARE @name varchar(100);

IF EXISTS (SELECT *FROM INSERTED i JOIN DELETED d ON i.id = d.id 
					WHERE i.faapproved IS NOT NULL 
					AND (d.faapproved IS NULL OR i.faapproved<>d.faapproved))
	begin
		select @id=i.id,@projectid=project.id  
		from INSERTED i
		JOIN project ON i.projectid = project.id;
		
		set @message='The Final Audit #'+CAST(@id as varchar(20))+' of Project #'+CAST(@projectid as varchar(100))+
		' was approved on '+ convert(varchar,GETDATE(),1);
		insert into changenotificationsmessage(message,date,url,changenotificationsid,programofficer,regionaldirector,programassistant,regionid)
		values(@message,GETDATE(),'auditsForm.do?cipeprojectId='+cast(@projectid as varchar(20)),3,null,null,null,null);
	end

end;
GO